<?php


class LocataireManager {

    public function __construct(Mypdo $db){
        $this->db= $db;
    }

    public function add(Locataire $locataire){
        $req = $this->db->prepare('INSERT INTO locataire ( type_loca, date_bail_deb_loca, date_bail_fin_loca, date_dern_rev_loca, id_pai ) VALUES
                  ( :type_loca, :date_bail_deb_loca, :date_bail_fin_loca, :date_dern_rev_loca, :id_pai )');
        $req->bindValue(':type_loca', $locataire->getTypeLoca(), PDO::PARAM_STR);
        $req->bindValue(':date_bail_deb_loca', $locataire->getDateBailDebLoca(), PDO::PARAM_STR);
        $req->bindValue(':date_bail_fin_loca', $locataire->getDateBailFinLoca(), PDO::PARAM_STR);
        $req->bindValue(':date_dern_rev_loca', $locataire->getDateBailRevLoca(), PDO::PARAM_STR);
        $req->bindValue(':id_pai', $locataire->getIdPai(), PDO::PARAM_STR);

        $req->execute();
        $id_loca = $this->db->lastInsertId();
        $pers = $this->toPersonne($locataire);
        $persManager = new PersonneManager($this->db);
        $id_pers = $persManager->add($pers);
        $partageManager = new PartageManager($this->db);
       // $id_loca = $this->getId($locataire);
        $partageManager->add(array('id_pers'=>$id_pers/*$persManager->getId($pers)*/,'id_loca'=>$id_loca));
        return $id_loca;

    }

    public function getList(){
        $listeLocataires = array();
        $sql = 'SELECT * FROM locataire ORDER BY id_loca DESC';
        $req = $this->db->query($sql);
        while ($locataire = $req->fetch(PDO::FETCH_OBJ)){
            $listeLocataires[] = new Locataire($locataire);
        }
        $req->closeCursor();
        return $listeLocataires;

    }

    public function getNbLoca(){
        $listeLocataires = $this->getList();
        $nbLoca = 0;
        foreach($listeLocataires as $locataire){
            $nbLoca++;
        }
        return $nbLoca;
    }

    public function toPersonne(Locataire $loca){
        return new Personne(array('nom_pers'=>$loca->getNomLoca(),'prenom_pers'=>$loca->getPrenomLoca(),'num_fix_pers'=>$loca->getNumFixLoca(),
        'num_port_pers'=>$loca->getNumPortLoca(),'email_pers'=>$loca->getEmailLoca(),'genre_pers'=>$loca->getGenreLoca(),'adresse_pers'=>$loca->getAdresseLoca()));
    }

    public function getLoca($id_loca)
    {
        $sql = 'SELECT * FROM immoloc.locataire WHERE id_loca = :id_loca';
        $req = $this->db->prepare($sql);
        $req->bindValue(':id_loca', $id_loca, PDO::PARAM_INT);
        $req->execute();
        $liste = $req->fetch(PDO::FETCH_ASSOC);
        $loc = new Locataire($liste);

        return $loc;
    }


    public function getId(Locataire $locataire)
    {
        
        $sql = 'SELECT id_loca FROM locataire WHERE id_pai = :id_pai AND date_bail_deb_loca = :date_bail_deb_loca AND date_bail_fin_loca = :date_bail_fin_loca AND date_dern_rev_loca = :date_dern_rev_loca';
        $req = $this->db->prepare($sql);
        $req->bindValue(':id_pai', $locataire->getIdPai(), PDO::PARAM_INT);
        $req->bindValue(':date_bail_deb_loca', $locataire->getDateBailDebLoca(), PDO::PARAM_STR);
        $req->bindValue(':date_bail_fin_loca', $locataire->getDateBailFinLoca(), PDO::PARAM_STR);
        $req->bindValue(':date_dern_rev_loca', $locataire->getDateBailRevLoca(), PDO::PARAM_STR);

        $req->execute();
        return $req->fetch(PDO::FETCH_OBJ)->id_loca;
    
    }

    public function getLocation($id_loca)
    {
        $sql = 'SELECT * FROM location l
        INNER JOIN occupe o ON l.id_loc = o.id_loc
        WHERE id_loca = :id_loca';
        $req = $this->db->prepare($sql);
        $req->bindValue(':id_loca', $id_loca, PDO::PARAM_INT);
        $req->execute();

        $sql2 = "SELECT count(l.id_loc) as nb FROM location l inner join occupe o on l.id_loc = o.id_loc WHERE id_loca = :id_loca";
        $req2 = $this->db->prepare($sql2);
        $req2->bindValue(':id_loca', $id_loca, PDO::PARAM_INT);
        $req2->execute();
        $nb = $req2->fetch(PDO::FETCH_OBJ)->nb;
        if($nb <= 1){
            return new Location($req->fetch(PDO::FETCH_ASSOC));
        }else{
            $listeloc = array();
            $liste = $req->fetchAll(PDO::FETCH_ASSOC);
            foreach ($liste as $value) {
                $listeloc[] = new Location($value);
            }
            return  $listeloc;
        }


    }

    public function getPersonne($id_loca)
    {
        $sql = 'SELECT * FROM personne p
        INNER JOIN partage pa ON pa.id_pers = p.id_pers
        WHERE pa.id_loca = :id_loca';
        $req = $this->db->prepare($sql);
        $req->bindValue(':id_loca', $id_loca, PDO::PARAM_INT);
        $req->execute();

        $sql2 = "SELECT count(p.id_pers) as nb FROM personne p inner join partage pa on p.id_pers = pa.id_pers WHERE id_loca = :id_loca";
        $req2 = $this->db->prepare($sql2);
        $req2->bindValue(':id_loca', $id_loca, PDO::PARAM_INT);
        $req2->execute();
        $nb = $req2->fetch(PDO::FETCH_OBJ)->nb;
        if($nb <= 1){
            $value = $req->fetch(PDO::FETCH_ASSOC);
            $pers = new Personne($value);
            $pers->setAdressePers(new Adresse(array('num_adresse'=>$value['num_ad_pers'],'voie_adresse'=>$value['voie_ad_pers'],'cp_adresse'=>$value['cp_ad_pers'],
                'ville_adresse'=>$value['ville_ad_pers'])));
            return $pers;
        }else{
            $listepers = array();
            $liste = $req->fetchAll(PDO::FETCH_ASSOC);

            foreach ($liste as $value) {
                $pers = new Personne($value);
                $pers->setAdressePers(new Adresse(array('num_adresse'=>$value['num_ad_pers'],'voie_adresse'=>$value['voie_ad_pers'],'cp_adresse'=>$value['cp_ad_pers'],
                    'ville_adresse'=>$value['ville_ad_pers'])));
                $listepers[] = $pers;
            }
            return  $listepers;
        }




    }



    public function delLoca($id_loca){
        $sql = 'DELETE FROM immoloc.locataire WHERE id_loca = :id_loca';
        $req = $this->db->prepare($sql);
        $req->bindValue(':id_loca', $id_loca, PDO::PARAM_INT);

        $req->execute();
    }

    public function  delLocaByIdPropri($id_propri){
    $sql = '
        SELECT DISTINCT id_loca FROM occupe o
        INNER JOIN location l ON o.id_loc = l.id_loc
        INNER JOIN immeuble i ON l.id_immeu = i.id_immeu
        WHERE i.id_propri = :id_propri';
    $req = $this->db->prepare($sql);
    $req->bindValue(':id_propri', $id_propri, PDO::PARAM_STR);

    $req->execute();

    $table = $req->fetchAll(PDO::FETCH_ASSOC);

    $occuManager = new OccupeManager($this->db);
    foreach ($table as $value) {
        $occuManager->delOccuByIdLoca($value['id_loca']);
    }


    foreach ($table as $value) {
        $this->delLoca($value['id_loca']);
    }


}
    public function  delLocaByIdImmeu($id_immeu){
        $sql = '
        SELECT DISTINCT id_loca FROM occupe o
        INNER JOIN location l ON o.id_loc = l.id_loc
        WHERE l.id_immeu = :id_immeu';
        $req = $this->db->prepare($sql);
        $req->bindValue(':id_immeu', $id_immeu, PDO::PARAM_STR);

        $req->execute();

        $table = $req->fetchAll(PDO::FETCH_ASSOC);

        $occuManager = new OccupeManager($this->db);
        foreach ($table as $value) {
            $occuManager->delOccuByIdLoca($value['id_loca']);
        }


        foreach ($table as $value) {
            $this->delLoca($value['id_loca']);
        }


    }
    public function delLocaByIdLoc($id_loc)
    {
        $sql = '
        SELECT DISTINCT id_loca FROM occupe
        WHERE id_loc = :id_loc';
        $req = $this->db->prepare($sql);
        $req->bindValue(':id_loc', $id_loc, PDO::PARAM_STR);

        $req->execute();

        $table = $req->fetchAll(PDO::FETCH_ASSOC);

        $occuManager = new OccupeManager($this->db);
        foreach ($table as $value) {
            $occuManager->delOccuByIdLoca($value['id_loca']);
        }


        foreach ($table as $value) {
            $this->delLoca($value['id_loca']);
        }

    }

    public function update(Locataire $loca)
    {
        $req = $this->db->prepare('UPDATE locataire SET type_loca = :type_loca, date_bail_deb_loca = :date_bail_deb_loca, date_bail_fin_loca = :date_bail_fin_loca, date_dern_rev_loca = :date_dern_rev_loca, id_pai = :id_pai WHERE id_loca = :id_loca');
        $req->bindValue(':type_loca', $loca->getTypeLoca(), PDO::PARAM_STR);
        $req->bindValue(':date_bail_deb_loca', $loca->getDateBailDebLoca(), PDO::PARAM_STR);
        $req->bindValue(':date_bail_fin_loca', $loca->getDateBailFinLoca(), PDO::PARAM_STR);
        $req->bindValue(':date_dern_rev_loca', $loca->getDateBailRevLoca(), PDO::PARAM_STR);
        $req->bindValue(':id_pai', $loca->getIdPai(), PDO::PARAM_STR);
        $req->bindValue(':id_loca', $loca->getIdLoca(), PDO::PARAM_INT);
        $req->execute();
    }


    public function statutLoca($id_loc,Locataire $loca){
        $sql = 'SELECT date_bail_deb_loca, date_bail_fin_loca FROM locataire l
        INNER JOIN occupe o ON o.id_loca = l.id_loca
        WHERE o.id_loc = :id_loc AND l.id_loca != :id_loca';
        $req = $this->db->prepare($sql);
        $req->bindValue(':id_loc', $id_loc, PDO::PARAM_INT);
        $req->bindValue(':id_loca', $loca->getIdLoca(), PDO::PARAM_INT);

        $req->execute();

        $liste = $req->fetchAll(PDO::FETCH_ASSOC);
        $stat = true;
        //var_dump($liste);
        $debLoca = new DateTime($loca->getDateBailDebLoca());
        $finLoca = new DateTime($loca->getDateBailFinLoca());

        foreach ($liste as $value) {
            $deb = new DateTime($value['date_bail_deb_loca']);
            $fin = new DateTime($value['date_bail_fin_loca']);

            //var_dump($deb<$debLoca&&$fin>$debLoca);var_dump($deb<$finLoca&&$fin>$finLoca);var_dump($deb>$debLoca&&$fin<$finLoca);
            if(($deb<$debLoca&&$fin>$debLoca)||($deb<$finLoca&&$fin>$finLoca)||($deb>$debLoca&&$fin<$finLoca)){
                $stat = false;
            }
        }

        return $stat;
    }

    public function getListByIdLoc($id_loc)
    {
        $listeLocataires = array();
        $sql = 'SELECT * FROM locataire la
        INNER JOIN occupe o ON o.id_loca = la.id_loca
        WHERE id_loc = :id_loc ORDER BY la.id_loca DESC';
        $req = $this->db->prepare($sql);
        $req->bindValue(':id_loc', $id_loc, PDO::PARAM_INT);
        $req->execute();
        while ($locataire = $req->fetch(PDO::FETCH_OBJ)){
            $listeLocataires[] = new Locataire($locataire);
        }
        $req->closeCursor();
        return $listeLocataires;

    }

    public function getListByMoisLoyer($date)
    {
        $listeLocataires = array();
        $sql = 'SELECT distinct la.id_loca FROM locataire la
        INNER JOIN loyer l ON l.id_loca = la.id_loca
        WHERE date_loyer LIKE :dates ORDER BY la.id_loca DESC';
        $req = $this->db->prepare($sql);
        $req->bindValue(':dates', $date.'-%', PDO::PARAM_STR);
        $req->execute();
        while ($locataire = $req->fetch(PDO::FETCH_OBJ)){
            $listeLocataires[] = new Locataire($locataire);
        }
        $req->closeCursor();
        return $listeLocataires;
    }

    public function getListByAppelLoyer($date)
    {
        $listeLocataires = array();
        $sql = 'SELECT * FROM locataire WHERE id_loca NOT IN
        (SELECT la.id_loca FROM locataire la
        INNER JOIN loyer l ON l.id_loca = la.id_loca
        WHERE date_loyer LIKE :dates
        ORDER BY la.id_loca DESC)  AND YEAR(date_bail_deb_loca) <= :anneedeb AND MONTH(date_bail_deb_loca) <= :moisdeb AND YEAR(date_bail_fin_loca) >= :anneefin AND MONTH(date_bail_fin_loca) >= :moisfin';
        $req = $this->db->prepare($sql);
        $req->bindValue(':dates', $date.'-%', PDO::PARAM_STR);

        $req->bindValue(':anneedeb', substr($date,0,-3), PDO::PARAM_STR);
        $req->bindValue(':anneefin', substr($date,0,-3), PDO::PARAM_STR);
        $req->bindValue(':moisdeb', substr($date,-2), PDO::PARAM_STR);
        $req->bindValue(':moisfin', substr($date,-2), PDO::PARAM_STR);

        $req->execute();
        while ($locataire = $req->fetch(PDO::FETCH_OBJ)){
            $listeLocataires[] = new Locataire($locataire);
        }
        $req->closeCursor();
        return $listeLocataires;
    }

    public function getLocationAppeler($id_loca,$date)
    {

        $sql = 'SELECT DISTINCT l.id_loc, id_immeu, appel_loc, descp_loc, encaissement_loc, exposition_loc, meuble_loc, montant_loc, nb_chambre_loc, nb_piece_loc, nom_loc, type_loc, surface_loc, statut_loc, tva_loc  FROM location l
        INNER JOIN loyer lo ON l.id_loc = lo.id_loc
        WHERE id_loca = :id_loca  AND date_loyer LIKE :dates';
        $req = $this->db->prepare($sql);
        $req->bindValue(':id_loca', $id_loca, PDO::PARAM_INT);
        $req->bindValue(':dates', $date.'-%', PDO::PARAM_STR);

        $req->execute();

        $sql2 = "SELECT DISTINCT count(l.id_loc) as nb FROM location l inner join loyer lo on l.id_loc = lo.id_loc WHERE id_loca = :id_loca AND date_loyer LIKE :dates";
        $req2 = $this->db->prepare($sql2);
        $req2->bindValue(':id_loca', $id_loca, PDO::PARAM_INT);
        $req2->bindValue(':dates', $date.'-%', PDO::PARAM_STR);

        $req2->execute();
        $nb = $req2->fetch(PDO::FETCH_OBJ)->nb;
        if($nb <= 1){
            return new Location($req->fetch(PDO::FETCH_ASSOC));
        }else{
            $listeloc = array();
            $liste = $req->fetchAll(PDO::FETCH_ASSOC);
            foreach ($liste as $value) {
                $listeloc[] = new Location($value);
            }
            return  $listeloc;
        }


    }

    public function getLocationQuit($id_loca, $date)
    {
        $sql = 'SELECT DISTINCT l.id_loc, id_immeu, appel_loc, descp_loc, encaissement_loc, exposition_loc, meuble_loc, montant_loc, nb_chambre_loc, nb_piece_loc, nom_loc, type_loc, surface_loc, statut_loc, tva_loc  FROM location l
        INNER JOIN loyer lo ON l.id_loc = lo.id_loc
        WHERE id_loca = :id_loca AND statut_loyer = 1 AND date_loyer LIKE :dates';
        $req = $this->db->prepare($sql);
        $req->bindValue(':id_loca', $id_loca, PDO::PARAM_INT);
        $req->bindValue(':dates', $date.'-%', PDO::PARAM_STR);

        $req->execute();

        $sql2 = "SELECT DISTINCT count(l.id_loc) as nb FROM location l inner join loyer lo on l.id_loc = lo.id_loc WHERE id_loca = :id_loca AND statut_loyer = 0 AND date_loyer LIKE :dates";
        $req2 = $this->db->prepare($sql2);
        $req2->bindValue(':id_loca', $id_loca, PDO::PARAM_INT);
        $req2->bindValue(':dates', $date.'-%', PDO::PARAM_STR);

        $req2->execute();
        $nb = $req2->fetch(PDO::FETCH_OBJ)->nb;
        if($nb <= 1){
            return new Location($req->fetch(PDO::FETCH_ASSOC));
        }else{
            $listeloc = array();
            $liste = $req->fetchAll(PDO::FETCH_ASSOC);
            foreach ($liste as $value) {
                $listeloc[] = new Location($value);
            }
            return  $listeloc;
        }

    }

} 